<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "https://raw.githubusercontent.com/rbatis/rbatis/master/rbatis-codegen/mybatis-3-mapper.dtd">
<mapper>
  <select id="select_role_list_by_user_id">
    `select sr.* from sys_role sr
    inner join sys_user_role sur on sr.role_id = sur.role_id
    where sur.user_id = #{user_id}`
  </select>

  <select id="select_roles_by_user_id">
    `select sr.* from sys_role sr 
    inner join sys_user_role sur on sr.role_id = sur.role_id
    where sur.user_id = #{id}`
  </select>

  <select id="select_roles_by_page">
    `select * from sys_role`
    <where>
      ` 1 = 1`
      <if test="role_name != ''">
        ` and role_name like '%${role_name}%' `
      </if>
      <if test="role_key != ''">
        ` and role_key = #{role_key} `
      </if>
      <if test="status != ''">
        ` and status = #{status} `
      </if>
      <if test="begin_time != ''">
        ` and create_time between #{begin_time} and #{end_time} `
      </if>
      ` limit #{page_num},#{page_size}`
    </where>
  </select>
  <select id="select_roles_count">
    `select count(*) from sys_role`
    <where>
      ` 1 = 1`
      <if test="role_name != ''">
        ` and role_name like '%${role_name}%' `
      </if>
      <if test="role_key != ''">
        ` and role_key = #{role_key} `
      </if>
      <if test="status != ''">
        ` and status = #{status} `
      </if>
      <if test="begin_time != ''">
        ` and create_time between #{begin_time} and #{end_time} `
      </if>
    </where>
  </select>


  <update id="update_role_status_by_id">
    `update sys_role set status=#{status}`
    <where>
      `role_id = #{role_id}`
    </where>
  </update>

  <delete id="del_role_by_id">
    `delete from sys_role
    <where>
      `role_id in ( ${role_id} )`
    </where>
  </delete>

  <select id="get_role_by_id">
    `select * from sys_role
    <where>
      `role_id in ( ${role_id} )`
    </where>
  </select>
  <select id="select_roles_list">
    `select * from sys_role where del_flag = 0`
  </select>
  <select id="select_roles_list_flag_and_status">
    `select * from sys_role where del_flag = 0 and status = 0 and role_id != 1`
  </select>
  <select id="select_roles_list_and_is_flag">
    `select * from sys_role where del_flag = 0 and status = 0 and role_id != 1`
  </select>
  <select id="select_roles_list_by_auth_id">
    `select su.* from sys_user su inner join sys_user_role sur on su.user_id = sur.user_id`
    <where>
      `sur.role_id = #{role_id}`

      <if test="user_name != ''">
        ` and su.user_name like '%${user_name}%'`
      </if>
      <if test="phone_number != ''">
        ` and su.phonenumber like '%${phone_number}%'`
      </if>
      ` limit #{page_num},#{page_size}`
    </where>
  </select>
  <select id="select_count_roles_list_by_auth_id">
    `select count(su.user_id) from sys_user su inner join sys_user_role sur on su.user_id = sur.user_id
    <where>
      `sur.role_id = #{role_id}`
      <if test="user_name != ''">
        ` and su.user_name like '%${user_name}%'`
      </if>
      <if test="phone_number != ''">
        ` and su.phonenumber like '%${phone_number}%'`
      </if>
    </where>
  </select>
  <select id="select_roles_list_not_in_role_id">
    `select su.* from sys_user su left join sys_user_role sur on su.user_id = sur.user_id`
    <where>
      `(sur.role_id != #{role_id} or sur.role_id is null) and su.user_id != 1`

      <if test="user_name != ''">
        ` and su.user_name like '%${user_name}%'`
      </if>
      <if test="phone_number != ''">
        ` and su.phonenumber like '%${phone_number}%'`
      </if>
      ` limit #{page_num},#{page_size}`
    </where>
  </select>
  <select id="select_count_roles_list_not_in_role_id">
    `select count(su.user_id) from sys_user su left join sys_user_role sur on su.user_id = sur.user_id
    <where>
      `(sur.role_id != #{role_id} or sur.role_id is null) and su.user_id != 1`
      <if test="user_name != ''">
        ` and su.user_name like '%${user_name}%'`
      </if>
      <if test="phone_number != ''">
        ` and su.phonenumber like '%${phone_number}%'`
      </if>
    </where>
  </select>
</mapper>